if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_banci_by_normal')
          and type in ('P','PC'))
   drop procedure bd_proc_banci_by_normal
go


create procedure bd_proc_banci_by_normal (@date_begin date,@date_end date,@depts varchar(max) = '') as
BEGIN
  --部门班次统计（（按正常班次正常出勤）
	--思路：1、创建返回临时表#returnTable
				--2、将depts分解插入#returnTable
				--3将参与统计的数据放入#temp_bd_kq_timecard
				--4、分别按部门分组加不同的条件，统计出指定数据 LEFT JOIN 全部查询语句，部门id关联，将指定数据更新到#returnTable
				--5、返回#returnTable；


	--1、创建返回临时表#returnTable
	-- 	部门	depName
	-- 	早早班	zaozoaban
	-- 	早班	zaoban
	-- 	中班	zhongban
	-- 	日8进8出	day8
	-- 	夜班	yeban
	-- 	夜8进8出	night8
	-- 	早中	zaozhong
	-- 	中夜	zhongye
	create table #returnTable(
		dep_serial bigint,
		dep_no varchar(20),
		dep_name varchar(200) COLLATE Chinese_PRC_CI_AS,
		zaozaoban int,
		zaoban int,
		zhongban int,
		day8 int,
		yeban int,
		night8 int,
		zaozhong int,
		zhongye int

	)

	--2、将depts分解插入#returnTable
	insert into #returnTable(dep_serial) select * from string_split(@depts,',');

	--3将参与统计的数据放入#temp_bd_kq_timecard (考勤状态:正常0，缺勤6，shift_id>0，放班 off_duty_hour>0，迟到4、早退5，请假leave_hours_total-off_duty_hour>0)
	-- 统计条件：status:考勤最终状态为正常=0，actual_hours：实出工时必须存在>0
	SELECT * into #temp_bd_kq_timecard  FROM  bd_kq_timecard
	WHERE dept_id in (select dep_serial from #returnTable) and card_date BETWEEN @date_begin and @date_end
		and status = 0 and actual_hours > 0


	--4、分别按部门分组加不同的条件，统计出指定数据 LEFT JOIN 全部查询语句，部门id关联，将指定数据更新到#returnTable
	--banci 类型 信息
	-- 	50000	早早班
	-- 	50001	早班
	-- 	50002	中班
	-- 	50003	日8进8出
	-- 	50004	夜班
	-- 	50005	夜8进8出
	-- 	50006	早中
	-- 	50007	中夜
	--

	update r
		set
				dep_name = d.dep_name,
				dep_no = d.dep_no,
				zaozaoban = isnull(a1.zaozaoban,0),
				zaoban  = isnull(a2.zaoban,0),
				zhongban = isnull(a3.zhongban,0),
				day8 = isnull(a4.day8,0),
				yeban = isnull(a5.yeban,0),
				night8 = isnull(a6.night8,0),
				zaozhong = isnull(a7.zaozhong,0),
				zhongye = isnull(a8.zhongye,0)
	from #returnTable r
	inner join dt_dep d on d.dep_serial = r.dep_serial

	--4.1 早早班
	left join
	(select dept_id,count(1) as zaozaoban
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50000 )
		GROUP BY dept_id) a1 on a1.dept_id  = r.dep_serial

	--4.2 早班次数
	left join
	(select dept_id,count(1) as zaoban
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50001 )
		GROUP BY dept_id) a2 on a2.dept_id  = r.dep_serial

	--4.3 中班次数
	left join
	(select dept_id,count(1) as zhongban
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50002 )
		GROUP BY dept_id) a3 on a3.dept_id  = r.dep_serial

	--4.4 日8进8出
	left join
	(select dept_id,count(1) as day8
		from #temp_bd_kq_timecard  where shift_id  in (select  id from  zt_banci where shift_type = 50003 )
		GROUP BY dept_id) a4 on a4.dept_id  = r.dep_serial

	--4.5 夜班次数
	left join
	(select dept_id,count(1) as yeban
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50004 )
		GROUP BY dept_id) a5 on a5.dept_id  = r.dep_serial

	--4.6 夜8进8出
	left join
	(select dept_id,count(1) as night8
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50005 )
		GROUP BY dept_id) a6 on a6.dept_id  = r.dep_serial

	--4.7 早中
	left join
	(select dept_id,count(1) as zaozhong
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50006 )
		GROUP BY dept_id) a7 on a7.dept_id  = r.dep_serial

	--4.8 中夜
	left join
	(select dept_id,count(1) as zhongye
		from #temp_bd_kq_timecard  where shift_id in (select  id from  zt_banci where shift_type = 50007 )
		GROUP BY dept_id) a8 on a8.dept_id  = r.dep_serial


	--5、返回#returnTable；
	select * from #returnTable order by dep_no

END
go
